{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "-v19XlsqP7mI",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d60b0715-5b9e-47f6-938b-f4cec095f607"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ],
      "source": [
        "#Code to mount Google Drive at Colab Notebook instance\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##**Install Langchain and Google Gemini related libraries**"
      ],
      "metadata": {
        "id": "6dNa79_nt5dZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install -q -U langchain==0.1.2\n",
        "!pip install -q -U google-generativeai==0.5.2\n",
        "!pip install -q -U langchain-google-genai==1.0.3"
      ],
      "metadata": {
        "id": "r76L6X4AQJEB",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "54bb3b04-197e-438d-8bad-94c9cea23ba8"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m803.6/803.6 kB\u001b[0m \u001b[31m7.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.0/2.0 MB\u001b[0m \u001b[31m52.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m302.9/302.9 kB\u001b[0m \u001b[31m24.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m49.3/49.3 kB\u001b[0m \u001b[31m4.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m49.3/49.3 kB\u001b[0m \u001b[31m4.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.0/2.0 MB\u001b[0m \u001b[31m28.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.0/2.0 MB\u001b[0m \u001b[31m44.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m2.0/2.0 MB\u001b[0m \u001b[31m44.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.9/1.9 MB\u001b[0m \u001b[31m50.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.9/1.9 MB\u001b[0m \u001b[31m46.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.9/1.9 MB\u001b[0m \u001b[31m47.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.9/1.9 MB\u001b[0m \u001b[31m45.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.9/1.9 MB\u001b[0m \u001b[31m71.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.8/1.8 MB\u001b[0m \u001b[31m66.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.8/1.8 MB\u001b[0m \u001b[31m75.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.8/1.8 MB\u001b[0m \u001b[31m66.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.8/1.8 MB\u001b[0m \u001b[31m65.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.8/1.8 MB\u001b[0m \u001b[31m65.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.7/1.7 MB\u001b[0m \u001b[31m67.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.7/1.7 MB\u001b[0m \u001b[31m65.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.7/1.7 MB\u001b[0m \u001b[31m67.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.7/1.7 MB\u001b[0m \u001b[31m72.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.7/1.7 MB\u001b[0m \u001b[31m70.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m302.9/302.9 kB\u001b[0m \u001b[31m28.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m302.8/302.8 kB\u001b[0m \u001b[31m28.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m303.0/303.0 kB\u001b[0m \u001b[31m28.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m302.9/302.9 kB\u001b[0m \u001b[31m28.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m302.2/302.2 kB\u001b[0m \u001b[31m26.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m299.3/299.3 kB\u001b[0m \u001b[31m28.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m291.3/291.3 kB\u001b[0m \u001b[31m23.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m290.2/290.2 kB\u001b[0m \u001b[31m30.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m289.1/289.1 kB\u001b[0m \u001b[31m27.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m287.5/287.5 kB\u001b[0m \u001b[31m28.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m278.4/278.4 kB\u001b[0m \u001b[31m25.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m276.8/276.8 kB\u001b[0m \u001b[31m25.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m276.6/276.6 kB\u001b[0m \u001b[31m25.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m279.2/279.2 kB\u001b[0m \u001b[31m24.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m274.6/274.6 kB\u001b[0m \u001b[31m24.9 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m273.9/273.9 kB\u001b[0m \u001b[31m25.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m273.0/273.0 kB\u001b[0m \u001b[31m26.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m271.6/271.6 kB\u001b[0m \u001b[31m27.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m269.1/269.1 kB\u001b[0m \u001b[31m24.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m260.9/260.9 kB\u001b[0m \u001b[31m28.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m258.8/258.8 kB\u001b[0m \u001b[31m25.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m256.9/256.9 kB\u001b[0m \u001b[31m27.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m252.6/252.6 kB\u001b[0m \u001b[31m26.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m252.4/252.4 kB\u001b[0m \u001b[31m22.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m250.8/250.8 kB\u001b[0m \u001b[31m27.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m246.4/246.4 kB\u001b[0m \u001b[31m24.8 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m242.1/242.1 kB\u001b[0m \u001b[31m24.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m241.3/241.3 kB\u001b[0m \u001b[31m24.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m241.2/241.2 kB\u001b[0m \u001b[31m23.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m239.4/239.4 kB\u001b[0m \u001b[31m26.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m238.5/238.5 kB\u001b[0m \u001b[31m19.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.6/1.6 MB\u001b[0m \u001b[31m67.0 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m1.6/1.6 MB\u001b[0m \u001b[31m64.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m238.4/238.4 kB\u001b[0m \u001b[31m25.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m238.5/238.5 kB\u001b[0m \u001b[31m23.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m53.0/53.0 kB\u001b[0m \u001b[31m5.7 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m121.0/121.0 kB\u001b[0m \u001b[31m5.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m142.5/142.5 kB\u001b[0m \u001b[31m7.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
            "\u001b[?25h\u001b[31mERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.\n",
            "langchain 0.1.2 requires langsmith<0.0.84,>=0.0.83, but you have langsmith 0.1.57 which is incompatible.\n",
            "langchain-community 0.0.18 requires langsmith<0.1,>=0.0.83, but you have langsmith 0.1.57 which is incompatible.\u001b[0m\u001b[31m\n",
            "\u001b[0m"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Get Gemini API Key from Secrets\n",
        "Set GEMINI_KEY secret key at Google Colab and get that here to runn Google Gemini LLM. You can get Google Gemini Key from following link https://makersuite.google.com/app/apikey"
      ],
      "metadata": {
        "id": "VkmAUJVzuG2T"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import userdata\n",
        "GOOGLE_API_KEY = userdata.get('GEMINI_KEY')"
      ],
      "metadata": {
        "id": "FXFDa9vmuCb4"
      },
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Copy chinhook.db to your Google Drive\n",
        "\n",
        "Now, chinhook.db is in our directory and we can interface with it using the SQLAlchemy-driven SQLDatabase class.\n",
        "\n",
        "Location of chinhook.db\n",
        "/content/drive/MyDrive/Colab Notebooks/chinook.db"
      ],
      "metadata": {
        "id": "OO1eAKgEm8OM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_community.utilities import SQLDatabase\n",
        "\n",
        "db = SQLDatabase.from_uri(\"sqlite:////content/drive/MyDrive/Colab Notebooks/chinook.db\")\n",
        "print(db.dialect)\n",
        "print(db.get_usable_table_names())\n",
        "db.run(\"SELECT * FROM Artists LIMIT 10;\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 87
        },
        "id": "V6Lo1RFGi-ut",
        "outputId": "b414cea4-1166-441c-dff0-aa219f54dc7b"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "sqlite\n",
            "['albums', 'artists', 'customers', 'employees', 'genres', 'invoice_items', 'invoices', 'media_types', 'playlist_track', 'playlists', 'tracks']\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "\"[(1, 'AC/DC'), (2, 'Accept'), (3, 'Aerosmith'), (4, 'Alanis Morissette'), (5, 'Alice In Chains'), (6, 'Antônio Carlos Jobim'), (7, 'Apocalyptica'), (8, 'Audioslave'), (9, 'BackBeat'), (10, 'Billy Cobham')]\""
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Chain\n",
        "Let’s create a simple chain that takes a question, turns it into a SQL query, executes the query, and uses the result to answer the original question."
      ],
      "metadata": {
        "id": "2FAUURh9nWIP"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Convert question to SQL query\n",
        "The first step in a SQL chain or agent is to take the user input and convert it to a SQL query. LangChain comes with a built-in chain for this: create_sql_query_chain"
      ],
      "metadata": {
        "id": "JrxSgy7MnelA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import re\n",
        "from langchain.chains import create_sql_query_chain\n",
        "from langchain_google_genai import ChatGoogleGenerativeAI\n",
        "from langchain_core.prompts import ChatPromptTemplate\n",
        "\n",
        "llm = ChatGoogleGenerativeAI(model=\"gemini-pro\", google_api_key=GOOGLE_API_KEY, convert_system_message_to_human=True, temperature=0.0)\n",
        "chain = create_sql_query_chain(llm, db)\n",
        "response = chain.invoke({\"question\": \"How many employees are there\"})\n",
        "\n",
        "# Generate regex pattern to remove prefix and suffix\n",
        "regex_pattern = r'^```sql\\n|\\n```$'\n",
        "\n",
        "# Remove prefix and suffix using regex to get proper SQL query\n",
        "modified_response = re.sub(regex_pattern, '', response)\n",
        "print(modified_response)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7Yzgu-IBjVjJ",
        "outputId": "07eb3aa9-2757-4609-e814-4928114f2718"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "SELECT COUNT(*) AS \"Number of Employees\"\n",
            "FROM employees;\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "db.run(modified_response)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "CB1nmUsHjwv8",
        "outputId": "e4e1c6f4-eeb8-43dc-c64b-e76d6d180e7f"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'[(8,)]'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Execute SQL query\n",
        "Now that we’ve generated a SQL query, we’ll want to execute it.\n",
        "\n",
        "We can use the QuerySQLDatabaseTool to easily add query execution to our chain:"
      ],
      "metadata": {
        "id": "xhKxXMd-nlct"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_community.tools.sql_database.tool import QuerySQLDataBaseTool\n",
        "from langchain_core.prompts import PromptTemplate\n",
        "\n",
        "execute_query = QuerySQLDataBaseTool(db=db)\n",
        "\n",
        "template = '''Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the {top_k} answer.\n",
        "Use the following format:\n",
        "\n",
        "Question: \"Question here\"\n",
        "\"SQL Query to run\"\n",
        "SQLResult: \"Result of the SQLQuery\"\n",
        "Answer: \"Final answer here\"\n",
        "\n",
        "Only use the following tables:\n",
        "\n",
        "{table_info}.\n",
        "\n",
        "Provide SQL query as simple string without any markdown.\n",
        "\n",
        "Question: {input}'''\n",
        "prompt = PromptTemplate.from_template(template)\n",
        "\n",
        "write_query = create_sql_query_chain(llm, db, prompt)\n",
        "chain = write_query | execute_query\n",
        "chain.invoke({\"question\": \"How many employees are there\"})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 70
        },
        "id": "r9_JsnIMj1o8",
        "outputId": "d0c94f3e-1523-49fa-97e2-12c304e8345e"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'[(8,)]'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Answer the question\n",
        "Now that we’ve got a way to automatically generate and execute queries, we just need to combine the original question and SQL query result to generate a final answer."
      ],
      "metadata": {
        "id": "TbItDxiEnxg2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from operator import itemgetter\n",
        "\n",
        "from langchain_core.output_parsers import StrOutputParser\n",
        "from langchain_core.prompts import PromptTemplate\n",
        "from langchain_core.runnables import RunnablePassthrough\n",
        "\n",
        "answer_prompt = PromptTemplate.from_template(\n",
        "\"\"\"Given the following user question, corresponding SQL query, and SQL result, answer the user question.\n",
        "\n",
        "Question: {question}\n",
        "{query}\n",
        "SQL Result: {result}\n",
        "Answer: \"\"\"\n",
        ")\n",
        "\n",
        "answer = answer_prompt | llm | StrOutputParser()\n",
        "chain = (\n",
        "    RunnablePassthrough.assign(query=write_query).assign(\n",
        "        result=itemgetter(\"query\") | execute_query\n",
        "    )\n",
        "    | answer\n",
        ")\n",
        "\n",
        "chain.invoke({\"question\": \"How many employees are there\"})"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 104
        },
        "id": "6MpiJVumkngx",
        "outputId": "ecc23fa9-9a7d-4180-af64-6de0f6e164e8"
      },
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n",
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'There are 8 employees.'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Agents\n",
        "LangChain has an SQL Agent which provides a more flexible way of interacting with SQL databases. The main advantages of using the SQL Agent are:\n",
        "\n",
        "It can answer questions based on the databases’ schema as well as on the databases’ content (like describing a specific table).\n",
        "It can recover from errors by running a generated query, catching the traceback and regenerating it correctly.\n",
        "It can answer questions that require multiple dependent queries.\n",
        "It will save tokens by only considering the schema from relevant tables.\n",
        "To initialize the agent, we use create_sql_agent function. This agent contains the SQLDatabaseToolkit which contains tools to:\n",
        "\n",
        "- Create and execute queries\n",
        "- Check query syntax\n",
        "- Retrieve table descriptions"
      ],
      "metadata": {
        "id": "R9bV_Z0bn5Kv"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_community.agent_toolkits import create_sql_agent\n",
        "from langchain.agents.agent_types import AgentType\n",
        "from langchain.agents.agent_toolkits import SQLDatabaseToolkit\n",
        "\n",
        "agent_executor = create_sql_agent(llm,\n",
        "                                  toolkit=SQLDatabaseToolkit(db=db, llm=llm),\n",
        "                                  agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,\n",
        "                                  verbose=True)"
      ],
      "metadata": {
        "id": "JBXPleWnn7XM"
      },
      "execution_count": 23,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "agent_executor.invoke(\n",
        "    {\n",
        "        \"input\": \"List the total sales per country. Which country's customers spent the most?\"\n",
        "    }\n",
        ")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "R0oVUppboHEe",
        "outputId": "d5701456-bc21-4e87-830b-daa8d7b834cd"
      },
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "\n",
            "\u001b[1m> Entering new SQL Agent Executor chain...\u001b[0m\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[32;1m\u001b[1;3mAction: sql_db_list_tables\n",
            "Action Input: \u001b[0m\u001b[38;5;200m\u001b[1;3malbums, artists, customers, employees, genres, invoice_items, invoices, media_types, playlist_track, playlists, tracks\u001b[0m"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[32;1m\u001b[1;3mAction: sql_db_query\n",
            "Action Input: SELECT country, SUM(total) AS total_sales FROM invoices GROUP BY country ORDER BY total_sales DESC\u001b[0m\u001b[36;1m\u001b[1;3mError: (sqlite3.OperationalError) no such column: country\n",
            "[SQL: SELECT country, SUM(total) AS total_sales FROM invoices GROUP BY country ORDER BY total_sales DESC]\n",
            "(Background on this error at: https://sqlalche.me/e/20/e3q8)\u001b[0m"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[32;1m\u001b[1;3mAction: sql_db_schema\n",
            "Action Input: invoices\u001b[0m\u001b[33;1m\u001b[1;3m\n",
            "CREATE TABLE invoices (\n",
            "\t\"InvoiceId\" INTEGER NOT NULL, \n",
            "\t\"CustomerId\" INTEGER NOT NULL, \n",
            "\t\"InvoiceDate\" DATETIME NOT NULL, \n",
            "\t\"BillingAddress\" NVARCHAR(70), \n",
            "\t\"BillingCity\" NVARCHAR(40), \n",
            "\t\"BillingState\" NVARCHAR(40), \n",
            "\t\"BillingCountry\" NVARCHAR(40), \n",
            "\t\"BillingPostalCode\" NVARCHAR(10), \n",
            "\t\"Total\" NUMERIC(10, 2) NOT NULL, \n",
            "\tPRIMARY KEY (\"InvoiceId\"), \n",
            "\tFOREIGN KEY(\"CustomerId\") REFERENCES customers (\"CustomerId\")\n",
            ")\n",
            "\n",
            "/*\n",
            "3 rows from invoices table:\n",
            "InvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n",
            "1\t2\t2009-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n",
            "2\t4\t2009-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n",
            "3\t8\t2009-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n",
            "*/\u001b[0m"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[32;1m\u001b[1;3mAction: sql_db_query\n",
            "Action Input: SELECT country, SUM(total) AS total_sales FROM invoices GROUP BY country ORDER BY total_sales DESC\u001b[0m\u001b[36;1m\u001b[1;3mError: (sqlite3.OperationalError) no such column: country\n",
            "[SQL: SELECT country, SUM(total) AS total_sales FROM invoices GROUP BY country ORDER BY total_sales DESC]\n",
            "(Background on this error at: https://sqlalche.me/e/20/e3q8)\u001b[0m"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[32;1m\u001b[1;3mAction: sql_db_schema\n",
            "Action Input: customers\u001b[0m\u001b[33;1m\u001b[1;3m\n",
            "CREATE TABLE customers (\n",
            "\t\"CustomerId\" INTEGER NOT NULL, \n",
            "\t\"FirstName\" NVARCHAR(40) NOT NULL, \n",
            "\t\"LastName\" NVARCHAR(20) NOT NULL, \n",
            "\t\"Company\" NVARCHAR(80), \n",
            "\t\"Address\" NVARCHAR(70), \n",
            "\t\"City\" NVARCHAR(40), \n",
            "\t\"State\" NVARCHAR(40), \n",
            "\t\"Country\" NVARCHAR(40), \n",
            "\t\"PostalCode\" NVARCHAR(10), \n",
            "\t\"Phone\" NVARCHAR(24), \n",
            "\t\"Fax\" NVARCHAR(24), \n",
            "\t\"Email\" NVARCHAR(60) NOT NULL, \n",
            "\t\"SupportRepId\" INTEGER, \n",
            "\tPRIMARY KEY (\"CustomerId\"), \n",
            "\tFOREIGN KEY(\"SupportRepId\") REFERENCES employees (\"EmployeeId\")\n",
            ")\n",
            "\n",
            "/*\n",
            "3 rows from customers table:\n",
            "CustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n",
            "1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n",
            "2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n",
            "3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n",
            "*/\u001b[0m"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[32;1m\u001b[1;3mAction: sql_db_query\n",
            "Action Input: SELECT c.country, SUM(i.total) AS total_sales FROM customers c JOIN invoices i ON c.customerid = i.customerid GROUP BY c.country ORDER BY total_sales DESC\u001b[0m\u001b[36;1m\u001b[1;3m[('USA', 523.0600000000004), ('Canada', 303.96), ('France', 195.09999999999994), ('Brazil', 190.1), ('Germany', 156.48), ('United Kingdom', 112.85999999999999), ('Czech Republic', 90.24), ('Portugal', 77.24), ('India', 75.25999999999999), ('Chile', 46.62), ('Ireland', 45.62), ('Hungary', 45.62), ('Austria', 42.62), ('Finland', 41.620000000000005), ('Netherlands', 40.62), ('Norway', 39.62), ('Sweden', 38.620000000000005), ('Poland', 37.620000000000005), ('Italy', 37.620000000000005), ('Denmark', 37.620000000000005), ('Australia', 37.620000000000005), ('Argentina', 37.620000000000005), ('Spain', 37.62), ('Belgium', 37.62)]\u001b[0m"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/langchain_google_genai/chat_models.py:326: UserWarning: Convert_system_message_to_human will be deprecated!\n",
            "  warnings.warn(\"Convert_system_message_to_human will be deprecated!\")\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\u001b[32;1m\u001b[1;3mFinal Answer: The country with the highest total sales is the USA, with $523.06 in total sales.\u001b[0m\n",
            "\n",
            "\u001b[1m> Finished chain.\u001b[0m\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'input': \"List the total sales per country. Which country's customers spent the most?\",\n",
              " 'output': 'The country with the highest total sales is the USA, with $523.06 in total sales.'}"
            ]
          },
          "metadata": {},
          "execution_count": 24
        }
      ]
    }
  ]
}